package com.jumi.microservice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jumi.microservice.domain.MessageMail;
import com.jumi.microservice.domain.vo.MessageUnreadVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface MessageMailMapper extends BaseMapper<MessageMail>{

    @Select("SELECT " +
"            ifnull(sum(" +
"                CASE template_title_type" +
"                WHEN 1" +
"                THEN 1" +
"                ELSE 0" +
"                END" +
"            ),0) logistics," +
"            ifnull(sum(" +
"                CASE template_title_type" +
"                WHEN 2" +
"                THEN 1" +
"                ELSE 0" +
"                END" +
"            ),0) activity," +
"            ifnull(sum(" +
"                CASE template_title_type" +
"                WHEN 3" +
"                THEN 1" +
"                ELSE 0" +
"                END" +
"            ),0) system," +
"            ifnull(sum(" +
"                CASE template_title_type" +
"                WHEN 4" +
"                THEN 1" +
"                ELSE 0" +
"                END" +
"            ),0) money" +
"       FROM `message_mail` WHERE " +
"       message_open = 0" +
"       AND re_member_id = #{uids}" +
"       AND is_del = 0")
    MessageUnreadVO selectUnread(@Param("uids") Long uids);
}
